package cn.wsalix.admin.repository;

import java.util.List;

import javax.persistence.QueryHint;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.QueryHints;
import org.springframework.data.repository.query.Param;

import cn.anlaser.repository.ParentRepository;
import cn.wsalix.admin.entity.SysMenu;
import cn.wsalix.admin.entity.SysRole;

public interface RoleRepository extends ParentRepository<SysRole> {

	@Query(value = "select u from SysRole u")
	@QueryHints({ @QueryHint(name = "org.hibernate.cacheable", value = "true") })
	public List<SysRole> findAllWithCache();

	//@Query(nativeQuery = true,value = "select user.* from (select admin_user.*,userrole.role_id as user_role from WOW_ADMIN_USER admin_user    left outer join (select ur.* from WOW_ADMIN_USER_ROLE ur  where ur.role_id = ?1) userrole on admin_user.id= userrole.user_id) user    where user_role is null")
	@Query(nativeQuery = true,value = "select role.id,role.code,role.name,userrole.role_id selected from RAY_SYS_ROLE role left outer join RAY_SYS_USER_ROLE userrole on role.id=userrole.role_id and userrole.user_id=?1")	
	List<Object[]> findNativeByUserId(Long userId);

	@Query(nativeQuery = true,value = "select role.* from (select admin_role.*,userrole.user_id as user_role from WOW_ADMIN_ROLE admin_role    left outer join (select ur.* from WOW_ADMIN_USER_ROLE ur  where ur.user_id = ?1) userrole on admin_role.id= userrole.role_id) role    where user_role is null")
	public List<SysRole> findUnRolesByUserId(@Param("userId") Long userId);

	@Query("select r from UserRole ur,SysRole r where ur.role.id = r.id and ur.user.id=:userId")
	public List<SysRole> findRolesByUserId(@Param("userId") Long userId);

	@Query("select r from RoleMenu rm,SysRole r where rm.role.id = r.id and rm.menu.id=:menuId")
	public List<SysRole> findRolesByMenuId(@Param("menuId")Long menuId);

	@Query(nativeQuery = true,value = "select role.* from (select admin_role.*,rolemenu.menu_id as role_menu from WOW_ADMIN_ROLE admin_role    left outer join (select ur.* from WOW_ADMIN_ROLE_MENU rm  where rm.menu_id = ?1) rolemenu on admin_role.id= rolemenu.role_id) role    where role_menu is null")
	public List<SysRole> findUnRolesByMenuId(@Param("menuId") Long menuId);

	@Query("select m from SysRole m where m.code =:code")
	public List<SysRole> findByCode(@Param("code") String code);
	
	@Query("select m from SysRole m where m.name =:name")
	public List<SysRole> findByName(@Param("name") String name);
}
